#include "server_api.h"

//创建数据库表
void create_datebase()
{
   //创建员工信息表
   char sql[256] = "create table if not exists staff_sys(id int,mode char,name char primary key,pwd char,age char,phone char,addr char,job char,time char,state char,salary int)";
   if(sqlite3_exec(db,sql,NULL,NULL,&errmsg)!=SQLITE_OK)
   {
	   printf("%s\n",errmsg);
   }
   else
   {
      printf("数据库员工信息表创建成功\n");
   } 
   //创建历史记录表
   char sql_h[256] = "create table if not exists history(info char)";
   if(sqlite3_exec(db,sql_h,NULL,NULL,&errmsg)!=SQLITE_OK)
   {
	   printf("%s\n",errmsg);
   }
   else
   {
      printf("数据库历史记录表创建成功\n");
   } 
}

//root登录
void login_root(Staff_Data_TypeDef *sdt,int acceptfd)
{
   char sqlstr[512] = {0};
   char **result;
   int nrow, ncolumn;
   sprintf(sqlstr, "select * from staff_sys where name='%s' and pwd='%s'", sdt->staff_info.name, sdt->staff_info.pwd);
   //printf("sqlstr = %s\n",sqlstr);
   if(sqlite3_get_table(db, sqlstr, &result, &nrow, &ncolumn, &errmsg) != SQLITE_OK)
   {
      printf("error : %s\n", errmsg);
   }
   //数据库中没有
   if(nrow==0)
   {
      int  id = 0;
      int mode = 0;
      char  name[20] = "slp"; 
      char  pwd[20] = "slp";
      int age = 0;
      char  phone[15] = "0";
      char  addr[128] = "0";
      char  job[32] = "0";
      char  time[12] = "0"; //2015.10.23
      char  state[8] = "0"; //T1~T10
      int  salary = 0;  

      memset(sqlstr,0,256);
      sprintf(sqlstr, "insert into staff_sys values(%d, %d,'%s','%s',%d,'%s','%s','%s','%s','%s',%d)",id,mode,name,pwd,age,phone,addr,job,time,state,salary);
      if(sqlite3_exec(db, sqlstr, NULL, NULL, &errmsg) != SQLITE_OK)
      {
         printf("插入初始管理员错误：%s\n", errmsg);
      }
      else
      {
         printf("成功：插入初始管理员成功\n");
      }
      strcpy(sdt->data, "已生成管理员");
      strcpy(sdt->data, "error");
   }
   else
   {
      if(atoi(result[ncolumn+1])==1)
      {
         strncpy(sdt->data,"no have power",256);
      }
      else{
         strncpy(sdt->data,"ok",256);

         //封装历史信息字符串
          strcpy(histroy_name,sdt->staff_info.name);
      }
      
   }
   send(acceptfd,sdt,sizeof(Staff_Data_TypeDef),0);
     
}
//user登录
void login_user(Staff_Data_TypeDef *sdt,int acceptfd)
{
   char sqlstr[512] = {0};
   char **result;
   int nrow, ncolumn;
   sprintf(sqlstr, "select * from staff_sys where name='%s' and pwd='%s'", sdt->staff_info.name, sdt->staff_info.pwd);
   if(sqlite3_get_table(db, sqlstr, &result, &nrow, &ncolumn, &errmsg) != SQLITE_OK)
   {
      printf("error : %s\n", errmsg);
   }
   //数据库中没有
   if(nrow==0)
   {
      strcpy(sdt->data, "name or password is wrony!!!");
   }
   else
   {
      strncpy(sdt->data,"ok",256);
      // //封装历史信息字符串
          strcpy(histroy_name,sdt->staff_info.name);
          printf("histroy_name:%s\n",histroy_name);
      //    sprintf(histroy_name_do,"%s：用户 %s 登录成功了",lock_time(),histroy_name);
      //    printf("histroy_name_do:%s\n",histroy_name_do);
      //    Insert_history_table(histroy_name_do);
   }

   send(acceptfd,sdt,sizeof(Staff_Data_TypeDef),0); 
}

//case判断按人名查找
void name_find(Staff_Data_TypeDef *sdt,int acceptfd)
{
   char sqlstr[512] = {0};
   char **result;
   int nrow, ncolumn;
   sprintf(sqlstr, "select * from staff_sys where name='%s'", sdt->staff_info.name);
   printf("sql = %s\n",sqlstr);
   if( sqlite3_get_table(db, sqlstr, &result, &nrow, &ncolumn, &errmsg)!= SQLITE_OK)
   {
      printf("error : %s\n", errmsg);
   }
   //数据库中没有
   if(nrow==0)
   {
      strcpy(sdt->data, "no have find");
   }
   else
   {
      strncpy(sdt->data,"ok",256);
   
   //send(acceptfd,sdt,sizeof(sdt),0);
   // printf("xxxxxx\n");
   // printf("result[ncolumn] = %d\n",atoi(result[ncolumn]));
   // printf("result[ncolumn+1] = %d\n",atoi(result[ncolumn+1]));
   // printf("result[ncolumn+2] = %s\n",result[ncolumn+2]);

   sdt->staff_info.id = atoi(result[ncolumn]);
   sdt->staff_info.mode = atoi(result[ncolumn+1]);
   strcpy(sdt->staff_info.name,result[ncolumn+2]);
   strcpy(sdt->staff_info.pwd,result[ncolumn+3]);
   sdt->staff_info.age = atoi(result[ncolumn+4]);
   strcpy(sdt->staff_info.phone,result[ncolumn+5]);
   strcpy(sdt->staff_info.addr,result[ncolumn+6]);
   strcpy(sdt->staff_info.job,result[ncolumn+7]);
   strcpy(sdt->staff_info.time,result[ncolumn+8]);
   strcpy(sdt->staff_info.state,result[ncolumn+9]);
   sdt->staff_info.salary = atoi(result[ncolumn+10]);

   // printf("id = %d\n",sdt->staff_info.id);
   // printf("mode = %d\n",sdt->staff_info.mode);
   // printf("name = %s\n",sdt->staff_info.name);
   // printf("pwd = %s\n",sdt->staff_info.pwd);
   // printf("age = %d\n",sdt->staff_info.age);
   // printf("phone = %s\n",sdt->staff_info.phone);
   // printf("addr = %s\n",sdt->staff_info.addr);
   // printf("job = %s\n",sdt->staff_info.job);
   // printf("time = %s\n",sdt->staff_info.time);
   // printf("state = %s\n",sdt->staff_info.state);
   // printf("id = %d\n",sdt->staff_info.id);
   //封装历史信息字符串
      //strcpy(histroy_name,sdt->staff_info.name);
      printf("histroy_name:%s\n",histroy_name);
      sprintf(histroy_name_do,"%s:管理员%s 查找了%s的信息",lock_time(),histroy_name,sdt->staff_info.name);
      printf("histroy_name_do:%s\n",histroy_name_do);
      Insert_history_table(histroy_name_do);
   }
   //strcpy(sdt->staff_info.phone,"111111111");
   send(acceptfd,sdt,sizeof(Staff_Data_TypeDef),0);
   printf("发送成功\n"); 
}

//case判断root查找所有
void each_all(Staff_Data_TypeDef *sdt,int acceptfd)
{
   char sqlstr[512] = {0};
   char **result;
   int nrow, ncolumn;
   sprintf(sqlstr, "select * from staff_sys");
   if(sqlite3_get_table(db, sqlstr, &result, &nrow, &ncolumn, &errmsg) != SQLITE_OK)
   {
      printf("error : %s\n", errmsg);
   }
   //数据库中没有
   if(nrow==0)
   {
      strcpy(sdt->data, "find_all error");
   }
   else
   {
      strncpy(sdt->data,"ok",256);  
      //send(acceptfd,sdt,sizeof(sdt),0);
      // printf("ncolumn = %d\n",ncolumn);
      // printf("nrow = %d\n",nrow);
      int i = 0;
      for(i=0;i<nrow;i++)
      {
         sdt->staff_info.id = atoi(result[ncolumn+0+i*ncolumn]);
         sdt->staff_info.mode = atoi(result[ncolumn+1+i*ncolumn]);
         strcpy(sdt->staff_info.name,result[ncolumn+2+i*ncolumn]);
         strcpy(sdt->staff_info.pwd,result[ncolumn+3+i*ncolumn]);
         sdt->staff_info.age = atoi(result[ncolumn+4+i*ncolumn]);
         strcpy(sdt->staff_info.phone,result[ncolumn+5+i*ncolumn]);
         strcpy(sdt->staff_info.addr,result[ncolumn+6+i*ncolumn]);
         strcpy(sdt->staff_info.job,result[ncolumn+7+i*ncolumn]);
         strcpy(sdt->staff_info.time,result[ncolumn+8+i*ncolumn]);
         strcpy(sdt->staff_info.state,result[ncolumn+9+i*ncolumn]);
         sdt->staff_info.salary = atoi(result[ncolumn+10+i*ncolumn]);

         if(-1!=send(acceptfd,sdt,sizeof(Staff_Data_TypeDef),0))
         {
            printf("%d\n",sdt->staff_info.id);
            printf("%d\n",sdt->staff_info.mode);
            printf("%s\n",sdt->staff_info.name);
            printf("%s\n",sdt->staff_info.pwd);
         }

      }
      strncpy(sdt->data,"end",256);
      //封装历史信息字符串
      //strcpy(histroy_name,sdt->staff_info.name);
      printf("histroy_name:%s\n",histroy_name);
      sprintf(histroy_name_do,"%s:管理员%s 查看了所有员工信息",lock_time(),histroy_name);
      printf("histroy_name_do:%s\n",histroy_name_do);
      Insert_history_table(histroy_name_do);
   }
   send(acceptfd,sdt,sizeof(Staff_Data_TypeDef),0);
}

//判断root添加用户操作
void add_user(Staff_Data_TypeDef *sdt,int acceptfd)
{
   char sqlstr[512] = {0};
   sprintf(sqlstr, "insert into staff_sys values(%d, %d,'%s','%s',%d,'%s','%s','%s','%s','%s',%d)",sdt->staff_info.id,sdt->staff_info.mode,sdt->staff_info.name,sdt->staff_info.pwd,sdt->staff_info.age,sdt->staff_info.phone,sdt->staff_info.addr,sdt->staff_info.job,sdt->staff_info.time,sdt->staff_info.state,sdt->staff_info.salary);
   if(sqlite3_exec(db, sqlstr, NULL, NULL, &errmsg) != SQLITE_OK)
   {
      sprintf(sdt->data, "user  already exist!!!");
   }
   else
   {
      strcpy(sdt->data, "ok");
      //封装历史信息字符串
      //strcpy(histroy_name,sdt->staff_info.name);
      printf("histroy_name:%s\n",histroy_name);
      sprintf(histroy_name_do,"%s：管理员%s 添加了%s用户",lock_time(),histroy_name,sdt->staff_info.name);
      printf("histroy_name_do:%s\n",histroy_name_do);
      Insert_history_table(histroy_name_do);
   }
   send(acceptfd,sdt,sizeof(Staff_Data_TypeDef),0);
   
}

//判断root是修改操作,函数内部完成根据id修改姓名
void id_modify_info(Staff_Data_TypeDef *sdt,int acceptfd)
{
   char sqlstr[512] = {0};
   char **result;
   sprintf(sqlstr, "update staff_sys set name=%s where id=%d;",sdt->staff_info.name,sdt->staff_info.id);
   if(sqlite3_exec(db, sqlstr, NULL, NULL, &errmsg) != SQLITE_OK)
   {
      sprintf(sdt->data, "user  already exist!!!");
   }
   else
   {
      strcpy(sdt->data, "ok");
      //封装历史信息字符串
      //strcpy(histroy_name,sdt->staff_info.name);
      printf("histroy_name:%s\n",histroy_name);
      sprintf(histroy_name_do,"%s：管理员%s修改了id为%d用户的姓名为%s",lock_time(),histroy_name,sdt->staff_info.id,sdt->staff_info.name);
      printf("histroy_name_do:%s\n",histroy_name_do);
      Insert_history_table(histroy_name_do);
   }
   if(-1!=send(acceptfd,sdt,sizeof(Staff_Data_TypeDef),0))
   {
      printf("发送成功\n");
   }
   
}


//判断root删除用户操作
void delete_user(Staff_Data_TypeDef *sdt,int acceptfd)
{
   char sqlstr[512] = {0};
   char **result;
   sprintf(sqlstr, "delete from staff_sys where id=%d;",sdt->staff_info.id);
   if(sqlite3_exec(db, sqlstr, NULL, NULL, &errmsg) != SQLITE_OK)
   {
      sprintf(sdt->data, "user  already exist!!!");
   }
   else
   {
      strcpy(sdt->data, "ok");
      //封装历史信息字符串
      //strcpy(histroy_name,sdt->staff_info.name);
      printf("histroy_name:%s\n",histroy_name);
      sprintf(histroy_name_do,"%s：管理员%s删除了id为%d用户",lock_time(),histroy_name,sdt->staff_info.id);
      printf("histroy_name_do:%s\n",histroy_name_do);
      Insert_history_table(histroy_name_do);
   }
   send(acceptfd,sdt,sizeof(Staff_Data_TypeDef),0);
}

//判断root查询历史记录操作
void search_histroy(Staff_Data_TypeDef *sdt,int acceptfd)
{
   char sqlstr[512] = {0};
   char **result;
   int nrow, ncolumn;
   sprintf(sqlstr, "select *from history");
   if(sqlite3_get_table(db, sqlstr, &result, &nrow, &ncolumn, &errmsg) != SQLITE_OK)
   {
      printf("error : %s\n", errmsg);
   }
   //数据库中没有
   if(nrow==0)
   {
      strcpy(sdt->data, "没有历史记录");
   }
   else
   {
      strcpy(sdt->data, "ok");
      printf("ncolumn = %d\n",ncolumn);
      printf("nrow = %d\n",nrow);
      int i = 0;
      for(i=0;i<nrow;i++)
      {
         strcpy(sdt->history,result[i+1]);
         if(-1!=send(acceptfd,sdt,sizeof(Staff_Data_TypeDef),0))
         {
            printf("%s\n",sdt->history);
         }

      }
      strncpy(sdt->data,"end",256);
      //封装历史信息字符串
      //strcpy(histroy_name,sdt->staff_info.name);
      printf("histroy_name:%s\n",histroy_name);
      sprintf(histroy_name_do,"%s：管理员%s查询了历史记录",lock_time(),histroy_name);
      printf("histroy_name_do:%s\n",histroy_name_do);
      Insert_history_table(histroy_name_do);

   }
   send(acceptfd,sdt,sizeof(Staff_Data_TypeDef),0);
}


//插入历史信息表函数
void Insert_history_table(char *info)
{
   char sqlstr[512] = {0};
   sprintf(sqlstr, "insert into history values('%s')",info);
   printf("sqlstr:%s\n",sqlstr);
   if(sqlite3_exec(db, sqlstr, NULL, NULL, &errmsg) != SQLITE_OK)
   {
      printf("插入历史信息失败\n");
   }
   else
   {
      printf("插入历史信息成功\n");
   }
}

//当前时间函数
char *lock_time(void)
{
   time_t mytime_s = time(NULL);
   if (mytime_s == -1 )
   {
      perror("time");
   }
   
   struct tm * mytm = localtime(&mytime_s);
   if (mytm == NULL)
   {
      printf("localtime error\n");
   }
   sprintf(time_s,"%d 年 %d 月 %d 日--%d : %d : %d \n",mytm->tm_year+1900,mytm->tm_mon+1,mytm->tm_mday,mytm->tm_hour,mytm->tm_min,mytm->tm_sec);
   printf("%s\n",time_s);
   return time_s;
}



//判断user查询操作
void user_search(Staff_Data_TypeDef *sdt,int acceptfd)
{
   char sqlstr[512] = {0};
   char **result;
   int nrow, ncolumn;
   sprintf(sqlstr, "select * from staff_sys where name='%s'",sdt->staff_info.name);
   printf("user name = %s",sdt->staff_info.name);
   printf("sql = %s\n",sqlstr);
   if( sqlite3_get_table(db, sqlstr, &result, &nrow, &ncolumn, &errmsg)!= SQLITE_OK)
   {
      printf("error : %s\n", errmsg);
   }
   //数据库中没有
   if(nrow==0)
   {
      strcpy(sdt->data, "no have find");
   }
   else
   {
      strncpy(sdt->data,"ok",256);
   
   sdt->staff_info.id = atoi(result[ncolumn]);
   sdt->staff_info.mode = atoi(result[ncolumn+1]);
   strcpy(sdt->staff_info.name,result[ncolumn+2]);
   strcpy(sdt->staff_info.pwd,result[ncolumn+3]);
   sdt->staff_info.age = atoi(result[ncolumn+4]);
   strcpy(sdt->staff_info.phone,result[ncolumn+5]);
   strcpy(sdt->staff_info.addr,result[ncolumn+6]);
   strcpy(sdt->staff_info.job,result[ncolumn+7]);
   strcpy(sdt->staff_info.time,result[ncolumn+8]);
   strcpy(sdt->staff_info.state,result[ncolumn+9]);
   sdt->staff_info.salary = atoi(result[ncolumn+10]);

   //封装历史信息字符串
      //strcpy(histroy_name,sdt->staff_info.name);
      printf("histroy_name:%s\n",histroy_name);
      sprintf(histroy_name_do,"%s:用户%s 查找了%s的信息",lock_time(),histroy_name,sdt->staff_info.name);
      printf("histroy_name_do:%s\n",histroy_name_do);
      Insert_history_table(histroy_name_do);
   }
   //strcpy(sdt->staff_info.phone,"111111111");
   send(acceptfd,sdt,sizeof(Staff_Data_TypeDef),0);
   printf("发送成功\n"); 
}

//判断user修改操作，函数内部根据id修改地址
void user_id_modify_info(Staff_Data_TypeDef *sdt,int acceptfd)
{
    char sqlstr[512] = {0};
   char **result;
   sprintf(sqlstr, "update staff_sys set addr='%s' where id=%d;",sdt->staff_info.addr,sdt->staff_info.id);
   printf("sqls = %s\n",sqlstr);
   if(sqlite3_exec(db, sqlstr, NULL, NULL, &errmsg) != SQLITE_OK)
   {
      sprintf(sdt->data, "user  already exist!!!");
   }
   else
   {
      strcpy(sdt->data, "ok");
      //封装历史信息字符串
      //strcpy(histroy_name,sdt->staff_info.name);
      printf("histroy_name:%s\n",histroy_name);
      sprintf(histroy_name_do,"%s：用户%s修改了自己的家庭住址为%s",lock_time(),sdt->staff_info.name,sdt->staff_info.addr);
      printf("histroy_name_do:%s\n",histroy_name_do);
      Insert_history_table(histroy_name_do);
   }
   send(acceptfd,sdt,sizeof(Staff_Data_TypeDef),0);
   printf("发送成功\n");
}